# Alex Gazmararian
# agazmararian@gmail.com

library(tidyverse)
library(tidylog)
library(readxl)
library(here)
library(modelsummary)
library(janitor)

ep_files <- list.files(here("data", "input", "eia_prices"), pattern = "\\.xlsx$", full.names = TRUE)

ep <- map_dfr(ep_files, function(x) {
  ep <- read_xlsx(x, skip = 2, progress = FALSE)
  ep <- clean_names(ep)
  ep <- ep %>%
    rename(
      eprice = average_price_cents_k_wh 
    ) %>%
    dplyr::select(eprice, state)
  # add year
  ep <- ep %>%
    mutate(year = str_extract(x, "\\d{4}"))
})

ep$year <- as.numeric(ep$year)

# Lag one year
ep$year_lag <- ep$year + 1

# state abbreviation
ep$state.abb <- state.abb[match(ep$state, state.name)]

write_csv(ep, here("data", "inter", "industrial_electricity_prices_state.csv"))
message("[OK] Saved industrial electricity prices data")
